﻿CREATE TABLE [CD].[InputLines] (
    [Id]                  INT             NOT NULL,
    [DepartmentId]        AS              ([Id]/(100000000)),
    [DocId]               INT             CONSTRAINT [DF_InputLines_DocId] DEFAULT ((0)) NOT NULL,
    [GoodsId]             INT             CONSTRAINT [DF_InputLines_GoodsId] DEFAULT ((1)) NOT NULL,
    [SupplierGoodsId]     INT             NULL,
    [ManufacturerPrice]   DECIMAL (19, 2) NULL,
    [SupplierPrice]       DECIMAL (19, 2) NULL,
    [SupplierSumNOTAX]    DECIMAL (19, 2) CONSTRAINT [DF_InputLines_SupplierSumNOTAX] DEFAULT ((0)) NOT NULL,
    [ImportMarkup]        FLOAT (53)      NULL,
    [SupplierMarkup]      FLOAT (53)      NULL,
    [GTDNumber]           VARCHAR (30)    NULL,
    [NDSValue]            DECIMAL (19, 2) NULL,
    [NDSTax]              TINYINT         NULL,
    [RetailMarkup]        FLOAT (53)      NULL,
    [BatchNumber]         VARCHAR (20)    NULL,
    [ReadyBefore]         SMALLDATETIME   NULL,
    [MadeDate]            SMALLDATETIME   NULL,
    [ReestrPrice]         DECIMAL (19, 2) NULL,
    [ReestrDate]          SMALLDATETIME   NULL,
    [CertificateNumber]   VARCHAR (80)    NULL,
    [CertificateDate]     SMALLDATETIME   NULL,
    [CertificateOrgId]    INT             NULL,
    [DocQntty]            INT             CONSTRAINT [DF_InputLines_DocQntty] DEFAULT ((1)) NOT NULL,
    [ContragentId]        INT             NULL,
    [ContragentDocDate]   SMALLDATETIME   NULL,
    [ContragentDocNumber] VARCHAR (20)    NULL,
    [Note]                VARCHAR (250)   NULL,
    [MD]                  DATETIME        CONSTRAINT [DF__InputLines__MD__795DFB40] DEFAULT (getdate()) NOT NULL,
    [MW]                  VARCHAR (50)    CONSTRAINT [DF__InputLines__MW__7A521F79] DEFAULT (suser_sname()) NOT NULL,
    CONSTRAINT [PK_InputLines] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_InputLines_Documents] FOREIGN KEY ([DocId]) REFERENCES [CA].[Documents] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_InputLines_Goods] FOREIGN KEY ([GoodsId]) REFERENCES [CA].[Goods] ([Id]),
    CONSTRAINT [FK_InputLines_GoodsVsContragents] FOREIGN KEY ([SupplierGoodsId]) REFERENCES [CA].[GoodsVsContragents] ([Id]) ON UPDATE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_InputLines_DocId]
    ON [CD].[InputLines]([DocId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_InputLines_DepartmentId]
    ON [CD].[InputLines]([DepartmentId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_InputLines_MD]
    ON [CD].[InputLines]([MD] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_InputLines_GoodsId]
    ON [CD].[InputLines]([GoodsId] ASC)
    INCLUDE([MD], [SupplierPrice], [DocId]);


GO
CREATE NONCLUSTERED INDEX [IX_InputLines_ContragentId]
    ON [CD].[InputLines]([ContragentId] ASC);


GO

CREATE TRIGGER CD.InputLinesDeleteLog ON CD.InputLines AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO [PR].[DocDeleteLog] (TableName, IdValue, MD, MW)
SELECT 'CD.InputLines', Id, getdate(), SYSTEM_USER FROM DELETED

END

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код подразделения', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'DepartmentId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Идентификатор документа, создавшего строку', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'DocId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Идентификатор перекодировки для кода товара поставщика', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'SupplierGoodsId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Цена производителя', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ManufacturerPrice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Цена оптовая (поставщика) с НДС', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'SupplierPrice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Сумма по стоке без налогов, в том числе без НДС', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'SupplierSumNOTAX';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наценка импортера %', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ImportMarkup';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наценка оптового звена %', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'SupplierMarkup';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'№ ГТД', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'GTDNumber';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Сумма НДС по строке от поставщика', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'NDSValue';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Ставка НДС по строке', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'NDSTax';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наценка аптеки', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'RetailMarkup';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Серия', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'BatchNumber';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Срок годности', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ReadyBefore';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Дата изготовления', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'MadeDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Зарегистрированная цена (Цена реестра)', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ReestrPrice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Дата регистрации цены (Дата реестра)', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ReestrDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'№ Сертификата', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'CertificateNumber';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Срок действия сертификата', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'CertificateDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код организации сертификации', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'CertificateOrgId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Количество по документу для Акта об излишках и недостачи', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'DocQntty';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для учета накладных поставщика при межскладском перемещении', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ContragentId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для учета накладных поставщика при межскладском перемещении', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ContragentDocDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Для учета накладных поставщика при межскладском перемещении', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'ContragentDocNumber';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Идентификатор строки расходной накладной', @level0type = N'SCHEMA', @level0name = N'CD', @level1type = N'TABLE', @level1name = N'InputLines', @level2type = N'COLUMN', @level2name = N'Note';

